package db

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql" //初始化
	"github.com/sirupsen/logrus"
	"sync"
	"time"
	"wlrIms/config"
)

// 定义结构体
type JsonData struct {
	Id   int    `json:"id"`
	Conn string `json:"conn"`
	Ask  string `json:"ask"`
	Ans  string `json:"ans"`
}

// 设置历史信息
type JsonStr struct {
	Ask string `json:"ask"`
	Ans string `json:"ans"`
}

type JsonDataStr struct {
	Prompt  string    `json:"prompt"`
	Conn    string    `json:"conn"`
	History []History `json:"history"`
}
type History struct {
	Ask string `json:"ask"`
	Ans string `json:"ans"`
}

var (
	db         *sql.DB
	Dbpv       config.Cfg
	dbRequests chan dbRequest
	stmtPool   sync.Pool
)

type dbRequest struct {
	sql  string
	data JsonData
}

func Keepalive(database *sql.DB) {
	tick := time.NewTicker(5 * time.Second)
	go func(t *time.Ticker) {
		for {
			select {
			case <-t.C:
				err := database.Ping()
				if err != nil {
					logrus.Error("Database connection failed. Please reconnect.")
				}
			}
		}
	}(tick)
}

// 判断mysql表是否存在
func tableIsExists(db *sql.DB, tableName string) bool {
	query := fmt.Sprintf("SELECT 1 FROM %s LIMIT 1", tableName)
	_, err := db.Exec(query)
	return err == nil
}
func InitDB() error {
	JsonParse := config.NewJsonStruct()
	Dbpv = JsonParse.LoadCfg()
	database := Dbpv.DataBase.Username + ":" + Dbpv.DataBase.Password + Dbpv.DataBase.HostDB + "/" + Dbpv.DataBase.DBname
	dsn := database + "?charset=utf8mb4&parseTime=True"
	logrus.Info("dsn:", dsn)
	var err error
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		logrus.Errorf("Failed to connect to the database: %v", err)
		return err
	}
	tables := []string{Dbpv.DataBase.Tblname}

	for _, tableName := range tables {
		tblIsExists := tableIsExists(db, tableName)
		if tblIsExists {
			logrus.Printf("Table '%s' already exists.\n", tableName)
		} else {
			err := createTable(db, tableName)
			if err != nil {
				logrus.Printf("Failed to create table '%s': %v\n", tableName, err)
			} else {
				logrus.Printf("Table '%s' created successfully.\n", tableName)
			}
		}
	}
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(5)

	err = db.Ping()
	if err != nil {
		logrus.Errorf("Database connection failed: %v", err)
		return err
	}

	Keepalive(db)

	// Initialize the database request channel and prepared statement pool
	dbRequests = make(chan dbRequest, 1000)
	stmtPool = sync.Pool{
		New: func() interface{} {
			stmt, err := db.Prepare("")
			if err != nil {
				logrus.Error("Failed to prepare statement: ", err)
			}
			return stmt
		},
	}

	// Start the goroutine to process database requests
	go processDBRequests()

	return nil
}
func processDBRequests() {
	for req := range dbRequests {
		if req.sql == "" {
			continue
		}

		stmt := stmtPool.Get().(*sql.Stmt)
		stmt.Exec(req.data.Conn, req.data.Ask, req.data.Ans)
		stmt.Close()
		stmtPool.Put(stmt)
	}
}

// 创建数据表
func createTable(db *sql.DB, tableName string) error {
	createTableStmt := fmt.Sprintf(`
	CREATE TABLE %s (
		id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		conn VARCHAR(255),
		ask VARCHAR(255),
		ans VARCHAR(255)
	)
`, tableName)
	_, err := db.Exec(createTableStmt)
	return err
}
func InsertDataBatches(sql string, datas []JsonData) bool {
	stmt, err := db.Prepare(sql)
	if err != nil {
		logrus.Error("Failed to prepare statement: ", err)
		return false
	}
	defer stmt.Close()

	tx, err := db.Begin()
	if err != nil {
		logrus.Error("Failed to begin transaction: ", err)
		return false
	}
	defer tx.Rollback()

	for _, msg := range datas {
		_, err = stmt.Exec(msg.Conn, msg.Ask, msg.Ans)
		if err != nil {
			logrus.Error("Failed to execute batch insert: ", err)
			return false
		}
	}
	err = tx.Commit()
	if err != nil {
		logrus.Error("Failed to commit transaction: ", err)
		return false
	}
	return true
}
func QueryToltalNumber(sql string) int {
	totalNum := 0
	err := db.QueryRow(sql).Scan(&totalNum)
	if err != nil {
		logrus.Error("Failed to get the total number: ", err)
		return 0
	}

	return totalNum
}

func DeleteDB(strSql string) bool {
	stmt, err := db.Prepare(strSql)
	if err != nil {
		logrus.Error("Failed to prepare statement: ", err)
		return false
	}
	defer stmt.Close()

	res, err := stmt.Exec()
	if err != nil {
		logrus.Error("Failed to delete records: ", err)
		return false
	}

	num, err := res.RowsAffected()
	if err != nil {
		logrus.Error("Failed to get the number of affected rows: ", err)
		return false
	}
	logrus.Info("Deleted records count: ", num)
	return true
}

// 查询数据信息，查询前5条数据
func QueryData(conn, prompt, sql string) (*JsonDataStr, bool) {
	res := &JsonDataStr{}
	//历史数据
	result := make([]History, 0)
	rows, err := db.Query(sql)
	if err != nil {
		logrus.Error("查询主表信息失败", err)
		return nil, false
	}
	defer rows.Close()

	for rows.Next() {
		tmp := JsonData{}
		historyTmp := History{}
		err := rows.Scan(&tmp.Id, &tmp.Conn, &tmp.Ask, &tmp.Ans)
		if err != nil {
			logrus.Error(err)
		} else {
			//历史
			historyTmp.Ask = tmp.Ask
			historyTmp.Ans = tmp.Ans
			result = append(result, historyTmp)
		}
	}
	res.Conn = conn
	res.Prompt = prompt
	res.History = result
	return res, true
}
